﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SQLite;

namespace SnmpForm.DbWrapper
{
    public class SQLDataOperate
    {
        //使用单件模式
        static SQLDataOperate _instance = null;
        public static SQLDataOperate Instance
        {
            get
            {
                if (_instance == null)
                {
                    _instance = new SQLDataOperate();
                }
                return _instance;
            }
        }

        private SQLiteHelper _mgr;

        /// <summary>
        /// 初始化数据库并打开
        /// </summary>
        public SQLDataOperate()
        {
            string dbpath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase
                + "db\\APManger";
            try
            {
                this._mgr = new SQLiteHelper(dbpath);
                this._mgr.Open();
            }
            catch { }
        }

        /// <summary>
        /// 检查表是否存在
        /// </summary>
        /// <param name="strTableName">表明</param>
        /// <returns>true or false</returns>
        public bool TableExists(string strTableName)
        {
            return this._mgr.TableExists(strTableName);
        }

        /// <summary>
        /// 写数据库日志system_log_info表
        /// </summary>
        /// <param name="level">级别</param>
        /// <param name="logtype">类型</param>
        /// <param name="loginfo">msg</param>
        /// <returns>true/false</returns>
        public bool WriteMessage(int level, int logtype, string loginfo, int userid=0)
        {
            bool ret = true;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("userid", userid);
            entity.Add("level", level);
            entity.Add("logtype", logtype);
            entity.Add("loginfo", loginfo);
            entity.Add("datetime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//2004-05-23 14:25:10.487
            try
            {
                this._mgr.Save("system_log_info", entity);
            }
            catch (SQLiteException es)
            {
                Console.WriteLine(es.Message);
                ret = false;
            }
            return ret;
        }
        /// <summary>
        /// 完整SQL语句执行方法
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回执行影响行数</returns>
        public int ExecuteSql(string sql)
        {
            int num = 0;
            try
            {
                num = this._mgr.ExecuteNonQuery(sql, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR, 
                    DbDataTypes.LogInfo.TYPE.SYSTEM, 
                    es.Message);
            }
            return num;
        }


        /*
         * 功能 
         *      插入AP信息到数据库 
         * 返回值：
         *      返回受影响的行数
         * 参数：
         *      IP、MAC、房间号等 list
         */
        public int SaveAPInfoList(List<object> list) //参数IP
        {
            int count = 0;
            foreach (object o in list)
            {
                Dictionary<string, object> entity = (Dictionary<string, object>)o;
                if (entity["floorid"].ToString().Length == 0)
                    entity["floorid"] = "unknown";
                try
                {
                    _mgr.Save("AP_list_info", entity);

                }
                catch (SQLiteException es)
                {
                    string loginfo = "路由器标识码（mark）: " + entity["mark"] + "; Msg: " + es.Message;
                    WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR, 
                        DbDataTypes.LogInfo.TYPE.SYSTEM,
                        loginfo);
                    continue;
                }
                count++;
            }
            return count;
        }

        public List<object> GetApInfoList(int _uid = 0, string _floorid = "")
        {
            List<object> list = null;
            string flooridCondition = "";
            string strSqlCmd = "";
            if (_floorid.Length != 0)
                flooridCondition = " where floorid = @floorid";

            strSqlCmd = "select * from AP_list_info " + flooridCondition;
            SQLiteParameter[] para = { new SQLiteParameter("floorid", _floorid) };
            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, para, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR, 
                    DbDataTypes.LogInfo.TYPE.SYSTEM, 
                    es.Message);
                return null;
            }
            return list;
        }

        public List<object> GetApInfoList(int _uid = 0, List<int> listApid = null)
        {
            List<object> list = null;
            if (listApid == null)
                return null;
            string strSqlCmd = "select * from AP_list_info  where 1=-1 ";
            try
            {
                foreach (int apid in listApid)
                {
                    strSqlCmd = strSqlCmd + " or id = " + apid;
                }
                list = this._mgr.ExecuteRow(strSqlCmd, null, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            return list;
        }

        /// <summary>
        /// 获取登入user相关信息
        /// </summary>
        /// <param name="_username"></param>
        /// <param name="_password"></param>
        /// <returns></returns>
        public DbDataTypes.User CheckLogin(string _username, string _password)
        {
            DbDataTypes.User user = null;
            List<object> list = null;

            string strSqlCmd = "select * from system_user_info where username = @username and password = @password ";
            SQLiteParameter[] para = { new SQLiteParameter("username",_username),
                                       new SQLiteParameter("password",_password)
                                     };
            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, para, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message); ;
                return null;
            }
            if (list.Count == 0)
            {
                return null;
            }
            user = new DbDataTypes.User();
            foreach (object o in list)
            {
                Dictionary<string, object> data = (Dictionary<string, object>)o;

                user.id = Convert.IsDBNull(data["id"]) ? -99 : Convert.ToInt32(data["id"]);
                user.username = data["username"].ToString();
                user.roletype = Convert.IsDBNull(data["roletype"]) ? -99 : Convert.ToInt32(data["roletype"]);
                user.password = data["password"].ToString();
                user.fullname = data["fullname"].ToString();
                user.lastlogintime = data["lastlogintime"].ToString();
                user.email = data["email"].ToString();
                user.phone = data["phone"].ToString();
                user.createtime = data["createtime"].ToString();
            }
            return user;
        }

        /// <summary>
        /// 获取楼层列表
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<string> GetFloorIds(int _uid)
        {
            List<string> listRoom = null;
            List<object> list = null;
            string strSqlCmd = "select DISTINCT floorid from AP_list_info;";
            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, null, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            listRoom = new List<string>();
            foreach (object o in list)
            {
                Dictionary<string, object> data = (Dictionary<string, object>)o;
                listRoom.Add(data["floorid"].ToString());
            }
            return listRoom;
        }

        /// <summary>
        /// 获取AP列表信息
        /// </summary>
        /// <param name="_uid"></param>
        /// <param name="_floorid">楼层，默认为0所有楼层</param>
        /// <returns></returns>
        public List<DbDataTypes.Ap> GetApList(int _uid = 0,string _floorid ="")
        {
            List<DbDataTypes.Ap> listAp = null;
            List<object> list = null;
            string strSqlCmd;
            try
            {
                if (_floorid == "")
                {
                    strSqlCmd = "select * from AP_list_info";
                    list = this._mgr.ExecuteRow(strSqlCmd, null, null);
                }
                else
                {
                    strSqlCmd = "select * from AP_list_info where floorid = @floorid";
                    SQLiteParameter[] para = { new SQLiteParameter("floorid", _floorid) };
                    list = this._mgr.ExecuteRow(strSqlCmd, para, null);
                }
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }

            listAp = new List<DbDataTypes.Ap>();
            foreach (object o in list)
            {
                DbDataTypes.Ap ap = new DbDataTypes.Ap();
                Dictionary<string, object> data = (Dictionary<string, object>)o;

                ap.id = Convert.IsDBNull(data["id"]) ? -99 : Convert.ToInt32(data["id"]);
                ap.floorid = data["floorid"].ToString();
                ap.roomid = data["roomid"].ToString();
                ap.mark = data["mark"].ToString();
                ap.manage_ip = data["manage_ip"].ToString();
                ap.landhcp = data["landhcp"].ToString();
                ap.ip = data["ip"].ToString();
                ap.netmask = data["netmask"].ToString();
                ap.gateway = data["gateway"].ToString();
                ap.mac = data["mac"].ToString();

                ap.snmp_community = data["snmp_community"].ToString();
                ap.snmp_password = data["snmp_password"].ToString();
                ap.snmp_port = Convert.IsDBNull(data["snmp_port"]) ? -99 : Convert.ToInt32(data["snmp_port"]);
                ap.snmp_user = data["snmp_user"].ToString();
                ap.snmp_version = Convert.IsDBNull(data["snmp_version"]) ? -99 : Convert.ToInt32(data["snmp_version"]);
                ap.software = data["software"].ToString();
                ap.hardware = data["hardware"].ToString();
                listAp.Add(ap);
            }

            return listAp;
        }

        public bool AddAP(int uid,DbDataTypes.Ap apInfo)
        {
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("mark", apInfo.mark);
            entity.Add("manage_ip", apInfo.manage_ip);
            entity.Add("landhcp", apInfo.landhcp);
            entity.Add("ip",apInfo.ip);
            entity.Add("netmask", apInfo.netmask);
            entity.Add("mac", apInfo.mac);
            if(apInfo.floorid.Equals(""))
                entity.Add("floorid","unknown");
            else
                entity.Add("floorid", apInfo.floorid);
            entity.Add("roomid", apInfo.roomid);
            entity.Add("snmp_community", apInfo.snmp_community);
            entity.Add("snmp_password", apInfo.snmp_password);
            entity.Add("snmp_port", apInfo.snmp_port);
            entity.Add("snmp_user", apInfo.snmp_user);
            try
            {
                _mgr.Save("AP_list_info", entity);
            }
            catch (SQLiteException es)
            {
                string loginfo = "路由器标示(mark): " + entity["mark"] + "; Msg: " + es.Message;
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return true;
        }

        public bool ModifyAP(int uid, DbDataTypes.Ap apInfo)
        {
            int count = -1;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("mark", apInfo.mark);
            entity.Add("manage_ip", apInfo.manage_ip);
            entity.Add("landhcp",apInfo.landhcp);
            entity.Add("ip",apInfo.ip);
            entity.Add("netmask", apInfo.netmask);
            entity.Add("gateway", apInfo.gateway);
            entity.Add("mac", apInfo.mac);
            if (apInfo.floorid.Equals(""))
                entity.Add("floorid", "unknown");
            else
                entity.Add("floorid", apInfo.floorid);
            entity.Add("roomid", apInfo.roomid);
            entity.Add("software", apInfo.software);
            entity.Add("hardware", apInfo.hardware);
            entity.Add("snmp_community", apInfo.snmp_community);
            entity.Add("snmp_password", apInfo.snmp_password);
            entity.Add("snmp_port", apInfo.snmp_port);
            entity.Add("snmp_user", apInfo.snmp_user);

            try
            {
                count = _mgr.Update("AP_list_info", entity, "id=@id", new System.Data.SQLite.SQLiteParameter[] { 
                    new SQLiteParameter("id",apInfo.id)});
            }
            catch (SQLiteException es)
            {
                string loginfo = "IP: " + entity["ip"] + "; MAC: " + entity["mac"] + "; Msg: " + es.Message;
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            if(count <= 0)
                return false;
            return true;
        }
        /// <summary>
        /// ap基本信息
        /// </summary>
        /// <param name="_uid"></param>
        /// <param name="_apid"></param>
        /// <returns></returns>
        public DbDataTypes.Ap GetAp(int _uid, int _apid)
        {
            DbDataTypes.Ap ap = null;
            Dictionary<string, object> entity = null;
  //          string strSqlCmd = "select * from AP_list_info where id = @id";
            try
            {
                entity = this._mgr.QueryOne("AP_list_info", "id", _apid);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            if (entity != null)
            {
                ap = new DbDataTypes.Ap();
                ap.id = Convert.IsDBNull(entity["id"]) ? -99 : Convert.ToInt32(entity["id"]);
                ap.mark = entity["mark"].ToString();
                ap.manage_ip = entity["manage_ip"].ToString();
                ap.landhcp = entity["landhcp"].ToString();
                ap.ip = entity["ip"].ToString();
                ap.netmask = entity["netmask"].ToString();
                ap.gateway = entity["gateway"].ToString();
                ap.mac = entity["mac"].ToString();
                ap.roomid = entity["roomid"].ToString();
                ap.snmp_community = entity["snmp_community"].ToString();
                ap.snmp_password = entity["snmp_password"].ToString();
                ap.snmp_port = Convert.IsDBNull(entity["snmp_port"]) ? -99 : Convert.ToInt32(entity["snmp_port"]);
                ap.snmp_user = entity["snmp_user"].ToString();
                ap.snmp_version = Convert.IsDBNull(entity["snmp_version"]) ? -99 : Convert.ToInt32(entity["snmp_version"]);
                ap.software = entity["software"].ToString();
                ap.floorid = entity["floorid"].ToString();
                ap.hardware = entity["hardware"].ToString();
            }
            return ap;
        }
        public DbDataTypes.Ap GetAp(int _uid, string _mark)
        {
            DbDataTypes.Ap ap = null;
            Dictionary<string, object> entity = null;
            //          string strSqlCmd = "select * from AP_list_info where id = @id";
            try
            {
                entity = this._mgr.QueryOne("AP_list_info", "mark", _mark);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            if (entity != null)
            {
                ap = new DbDataTypes.Ap();
                ap.id = Convert.IsDBNull(entity["id"]) ? -99 : Convert.ToInt32(entity["id"]);
                ap.mark = entity["mark"].ToString();
                ap.manage_ip = entity["manage_ip"].ToString();
                ap.landhcp = entity["landhcp"].ToString();
                ap.ip = entity["ip"].ToString();
                ap.netmask = entity["netmask"].ToString();
                ap.gateway = entity["gateway"].ToString();
                ap.mac = entity["mac"].ToString();
                ap.roomid = entity["roomid"].ToString();
                ap.snmp_community = entity["snmp_community"].ToString();
                ap.snmp_password = entity["snmp_password"].ToString();
                ap.snmp_port = Convert.IsDBNull(entity["snmp_port"]) ? -99 : Convert.ToInt32(entity["snmp_port"]);
                ap.snmp_user = entity["snmp_user"].ToString();
                ap.snmp_version = Convert.IsDBNull(entity["snmp_version"]) ? -99 : Convert.ToInt32(entity["snmp_version"]);
                ap.software = entity["software"].ToString();
                ap.floorid = entity["floorid"].ToString();
                ap.hardware = entity["hardware"].ToString();
            }
            return ap;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="_apid"></param>
        /// <param name="action">0 清空</param>
        /// <returns></returns>
        public bool DeleteAp(int uid, int _apid,int action = 1)
        {
            int count = 0;
            try
            {
                if (action == 0)
                {
                    _mgr.Delete("AP_wireless_info",null,null);
                    _mgr.Delete("AP_wireless_security",null,null);
                    count = _mgr.Delete("AP_list_info", null, null);
                }
                else
                {
                    SQLiteParameter[] parameter = new SQLiteParameter[] { new SQLiteParameter("apid", _apid) };
                    _mgr.Delete("AP_wireless_info", "apid=@apid", parameter);
                    _mgr.Delete("AP_wireless_security", "apid=@apid", parameter);
                    count = _mgr.Delete("AP_list_info", "id=@apid", parameter);
                }
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }

            if (count <= 0)
                return false;
            return true;
        }

        public bool DeleteAp(int uid, List<int> _listapid)
        {
            int count = -1;
            string list_info_sqlstm = "delete from AP_list_info where 1=-1 ";
            string wireless_info_sqlstm = "delete from AP_wireless_info where 1=-1 ";
            string wireless_security_sqlstm = "delete from AP_wireless_security where 1=-1 ";
            try
            {
                foreach (int apid in _listapid)
                {
                    list_info_sqlstm = list_info_sqlstm + " or id = " + apid;
                    wireless_info_sqlstm = wireless_info_sqlstm + " or id = " + apid;
                    wireless_security_sqlstm = wireless_security_sqlstm + " or id = " + apid;
                }
                _mgr.ExecuteNonQuery(wireless_info_sqlstm, null);
                _mgr.ExecuteNonQuery(wireless_security_sqlstm, null);
                count = _mgr.ExecuteNonQuery(list_info_sqlstm, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return true;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="wi"></param>
        /// <returns></returns>
        public bool SetWirelessInfo(int uid, DbDataTypes.WirelessInfo wi)
        {
            int count = -1;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("apid", wi.apid);
            entity.Add("ssid", wi.ssid);
            entity.Add("mac", wi.mac);
            entity.Add("enable", wi.enable);
            entity.Add("power", wi.power);
            entity.Add("wmm", wi.wmm);
            entity.Add("short_gi", wi.short_gi);
            entity.Add("signalstrength", wi.signalstrength);
            entity.Add("workmode", wi.workmode);
            entity.Add("wds", wi.wds);
            entity.Add("channel", wi.channel);
            entity.Add("wirelessmode", wi.wirelessmode);

            try
            {
                count = _mgr.Update("AP_wireless_info", entity, "apid=@apid", new System.Data.SQLite.SQLiteParameter[] { 
                        new SQLiteParameter("apid",wi.apid)});
                if (count <= 0)
                    _mgr.Save("AP_wireless_info", entity);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return true;
        }
        /// <summary>
        /// 无线基本信息
        /// </summary>
        /// <param name="apid"></param>
        /// <returns></returns>
        public DbDataTypes.WirelessInfo GetWirelessInfo(int apid)
        {
            DbWrapper.DbDataTypes.WirelessInfo info = null;

            Dictionary<string, object> entity = null;

            try
            {
                entity = this._mgr.QueryOne("AP_wireless_info", "apid", apid);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            if (entity != null)
            {
                info = new DbWrapper.DbDataTypes.WirelessInfo();

  //              info.id = entity["id"].ToString()==null? -99:Int32.Parse(entity["id"].ToString());
                info.id = Convert.IsDBNull(entity["id"]) ? -99 : Convert.ToInt32(entity["id"]);
                info.apid = Convert.IsDBNull(entity["apid"]) ? -99 : Convert.ToInt32(entity["apid"]);
                info.ssid = entity["ssid"].ToString();
                info.mac = entity["mac"].ToString();
                info.enable = Convert.IsDBNull(entity["enable"]) ? false : Convert.ToBoolean(entity["enable"]);
                info.wds = Convert.IsDBNull(entity["wds"]) ? false : Convert.ToBoolean(entity["wds"]);
                info.power = Convert.IsDBNull(entity["power"]) ? -99 : Convert.ToInt32(entity["power"]);
                info.signalstrength = entity["signalstrength"].ToString();
                info.workmode = entity["workmode"].ToString();
                info.channel = Convert.IsDBNull(entity["channel"]) ? -99 : Convert.ToInt32(entity["channel"]);
                info.wirelessmode = entity["wirelessmode"].ToString();
 //               info.short_gi = Convert.IsDBNull(entity["short_gi"])? false : bool.Parse(entity["short_gi"].ToString());
                info.short_gi = Convert.IsDBNull(entity["short_gi"]) ? false : Convert.ToBoolean(entity["short_gi"]);
                info.wmm = Convert.IsDBNull(entity["wmm"])? false : Convert.ToBoolean(entity["wmm"]);
            }
            return info;
        }


        public bool SetWirelessSecurity(int uid,DbDataTypes.WirelessSecurity ws)
        {
            int count = -1;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("apid", ws.apid);
            entity.Add("psk_key", ws.psk_key);
            entity.Add("secmode", ws.secmode);
            entity.Add("wep_key1", ws.wep_key[0].key);
            entity.Add("wep_passwd_mode1", ws.wep_key[0].passwd_mode);
            entity.Add("wep_key2", ws.wep_key[1].key);
            entity.Add("wep_passwd_mode2", ws.wep_key[1].passwd_mode);
            entity.Add("wep_key3", ws.wep_key[2].key);
            entity.Add("wep_passwd_mode3", ws.wep_key[2].passwd_mode);
            entity.Add("wep_key4", ws.wep_key[3].key);
            entity.Add("wep_passwd_mode4", ws.wep_key[3].passwd_mode);
            entity.Add("wep_mode", ws.wep_mode);
            entity.Add("wep_passwd_pattern", ws.wep_passwd_pattern);
            entity.Add("wep_key_index", ws.wep_key_index);
            entity.Add("wpa_cypher", ws.wpa_cypher);
            entity.Add("wpa_group_rekey", ws.wpa_group_rekey);
            entity.Add("wpa_mode", ws.wpa_mode);

            try
            {
                count = _mgr.Update("AP_wireless_security", entity, "apid=@apid", new System.Data.SQLite.SQLiteParameter[] { 
                        new SQLiteParameter("apid",ws.apid)});
                if (count <= 0)
                    _mgr.Save("AP_wireless_security", entity);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return true;
        }

        /// <summary>
        /// 无线安全配置
        /// </summary>
        /// <param name="apid"></param>
        /// <returns></returns>
        public DbDataTypes.WirelessSecurity GetWirelessSecurity(int apid)
        {
            DbWrapper.DbDataTypes.WirelessSecurity info = null;
            Dictionary<string, object> entity = null;

            try
            {
                entity = this._mgr.QueryOne("AP_wireless_security", "apid", apid);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            if (entity != null)
            {
                info = new DbWrapper.DbDataTypes.WirelessSecurity();

                info.id = Convert.IsDBNull(entity["id"]) ? -99 : Convert.ToInt32(entity["id"]);
                info.apid = Convert.IsDBNull(entity["apid"]) ? -99 : Convert.ToInt32(entity["apid"]);
                info.secmode = entity["secmode"].ToString();

                info.psk_key = entity["psk_key"].ToString();
                info.wpa_cypher = entity["wpa_cypher"].ToString();
                info.wpa_group_rekey = Convert.IsDBNull(entity["wpa_group_rekey"]) ? -99 : Convert.ToInt32(entity["wpa_group_rekey"]);
                info.wpa_mode = Convert.IsDBNull(entity["wpa_mode"]) ? -99 : Convert.ToInt32(entity["wpa_mode"]);

                info.wep_passwd_pattern = Convert.IsDBNull(entity["wep_passwd_pattern"]) ? -99 : Convert.ToInt32(entity["wep_passwd_pattern"]);
                info.wep_mode = Convert.IsDBNull(entity["id"]) ? -99 : Convert.ToInt32(entity["id"]);
                info.wep_key_index = Convert.IsDBNull(entity["wep_key_index"]) ? -99 : Convert.ToInt32(entity["wep_key_index"]);
                info.wep_key[0].key = entity["wep_key1"].ToString();
                info.wep_key[0].passwd_mode = Convert.IsDBNull(entity["wep_passwd_mode1"]) ? -99 : Convert.ToInt32(entity["wep_passwd_mode1"]);
                info.wep_key[1].key = entity["wep_key2"].ToString();
                info.wep_key[1].passwd_mode = Convert.IsDBNull(entity["wep_passwd_mode2"]) ? -99 : Convert.ToInt32(entity["wep_passwd_mode2"]);
                info.wep_key[2].key = entity["wep_key3"].ToString();
                info.wep_key[2].passwd_mode = Convert.IsDBNull(entity["wep_passwd_mode3"]) ? -99 : Convert.ToInt32(entity["wep_passwd_mode3"]);
                info.wep_key[3].key = entity["wep_key4"].ToString();
                info.wep_key[3].passwd_mode = Convert.IsDBNull(entity["wep_passwd_mode4"]) ? -99 : Convert.ToInt32(entity["wep_passwd_mode4"]);
            }
            return info;
        }


        /// <summary>
        /// 系统用户列表
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<DbDataTypes.User> GetUserList(int uid)
        {
            List<DbDataTypes.User> listUser = null;
            List<object> list = null;
            string strSqlCmd = "select * from system_user_info";
            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, null, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            listUser = new List<DbDataTypes.User>();
            foreach (object o in list)
            {
                DbDataTypes.User user = new DbDataTypes.User();
                Dictionary<string, object> data = (Dictionary<string, object>)o;

                user.id = Convert.IsDBNull(data["id"]) ? -99 : Convert.ToInt32(data["id"]);
                user.username = data["username"].ToString();
                user.roletype = Convert.IsDBNull(data["roletype"]) ? -99 : Convert.ToInt32(data["roletype"]);
                user.phone = data["phone"].ToString();
                user.password = data["password"].ToString();
                user.lastlogintime = data["lastlogintime"].ToString();
                user.fullname = data["fullname"].ToString();
                user.email = data["email"].ToString();
                user.createtime = data["createtime"].ToString();

                listUser.Add(user);
            }
            return listUser;
        }

        public bool CreateUser(int uid, DbDataTypes.User userInfo)
        {
            bool ret = false;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("username", userInfo.username);
            entity.Add("roletype", userInfo.roletype);
            entity.Add("phone", userInfo.phone);
            entity.Add("password", userInfo.password);
            entity.Add("email", userInfo.email);
            entity.Add("fullname", userInfo.fullname);
            entity.Add("createtime", DateTime.Now.ToString("s"));
            try
            {
                ret =_mgr.Save("system_user_info", entity);
            }
            catch (SQLiteException es)
            {
                string loginfo = "user: " + userInfo.username + es.Message;
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return ret;
        }

        public bool ModifyUser(int uid, DbDataTypes.User userInfo)
        {
            int count = -1;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("username", userInfo.username);
            entity.Add("roletype", userInfo.roletype);
            entity.Add("phone", userInfo.phone);
            entity.Add("password", userInfo.password);
            entity.Add("email", userInfo.email);
            entity.Add("fullname", userInfo.fullname);
            entity.Add("deleted", userInfo.deleted);

            try
            {
                count = _mgr.Update("system_user_info", entity, "id=@id", new System.Data.SQLite.SQLiteParameter[] { 
                    new SQLiteParameter("id",userInfo.id)});
            }
            catch (SQLiteException es)
            {
                string loginfo = "user: " + userInfo.username + es.Message;
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            if(count <= 0)
                return false;
            return true;
        }

        public DbDataTypes.User GetUser(int uid, int userid)
        {
            DbDataTypes.User user = null;
            Dictionary<string, object> entity = null;

            try
            {
                entity = this._mgr.QueryOne("system_user_info", "id", userid);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            if (entity != null)
            {
                user = new DbDataTypes.User();
                user.id = userid;
                user.username = entity["username"].ToString();
                user.roletype = Convert.IsDBNull(entity["roletype"]) ? -99 : Convert.ToInt32(entity["roletype"]);
                user.phone = entity["phone"].ToString();
                user.lastlogintime = entity["lastlogintime"].ToString();
                user.fullname = entity["fullname"].ToString();
                user.email = entity["email"].ToString();
                user.createtime = entity["createtime"].ToString();
            }
            return user;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="userid"></param>
        /// <param name="action">0 清空</param>
        /// <returns></returns>
        public bool DeleteUser(int uid, int userid,int action = 1)
        {
            int count = 0;
            try{
                if (action == 0)
                {
                    count = _mgr.Delete("system_user_info", null, null);
                }
                else
                {
                    count = _mgr.Delete("system_user_info", "id=@id", new SQLiteParameter[] {
                    new SQLiteParameter("id",userid)});
                }
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }

            if(count <= 0)
                return false;
            return true;
        }

        public bool SetFailureAP(int uid, DbDataTypes.FailureApInfo ApInfo)
        {
            bool ret = false;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("failureid", ApInfo.failureid);
            entity.Add("apid", ApInfo.apid);
            entity.Add("description", ApInfo.description);
            entity.Add("createtime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            try
            {
                ret = _mgr.Save("AP_failure_assoc", entity);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return true;
        }
        public List<DbDataTypes.FailureInfo> GetFailureInfo(int uid)
        {
            List<DbDataTypes.FailureInfo> listFault = null;
            List<object> list = null;

            string strSqlCmd = "select * from AP_failure_list";
            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, null, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            listFault = new List<DbDataTypes.FailureInfo>();
            foreach (object o in list)
            {
                DbDataTypes.FailureInfo info = new DbDataTypes.FailureInfo();
                Dictionary<string, object> data = (Dictionary<string, object>)o;


                info.id = Convert.IsDBNull(data["id"]) ? -99 : Convert.ToInt32(data["id"]);
                info.level = Convert.IsDBNull(data["level"]) ? -99 : Convert.ToInt32(data["level"]);
                info.failurename = data["failurename"].ToString();
                info.description = data["failuredescription"].ToString();
                listFault.Add(info);
            }
            return listFault;
        }

        public List<DbDataTypes.FailureApInfo> GetFailureAPList(
            string floorid = "",
            string ip = "",
            string mark = "",
            int _uid = 0)
        {
            List<DbDataTypes.FailureApInfo> listFailureAP = null;
            List<object> list = null;

            string flooridCondition = "";
            string ipCondition = "";
            string markCondition = "";

            if (floorid != null && floorid.Length != 0)
                flooridCondition = " and l.floorid = @floorid ";
            if (ip != null && ip.Length != 0)
                ipCondition = " and l.manage_ip = @ip";
            if (mark != null && mark.Length != 0)
                markCondition = " and l.mark like @mark";

            string strSqlCmd = "select distinct apid , failureid  from AP_failure_assoc a left join  AP_list_info l "+
                "on a.apid = l.id where 1=1" +
              flooridCondition + ipCondition + markCondition;

            SQLiteParameter[] para = { new SQLiteParameter("floorid",floorid),
                                       new SQLiteParameter("ip",ip),
                                       new SQLiteParameter("mark","%"+mark+"%"),
                                     };

            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, para, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            listFailureAP = new List<DbDataTypes.FailureApInfo>();
            foreach (object o in list)
            {
                DbDataTypes.FailureApInfo failureAp = new DbDataTypes.FailureApInfo();
                Dictionary<string, object> data = (Dictionary<string, object>)o;

                failureAp.apid = Convert.IsDBNull(data["apid"]) ? -99 : Convert.ToInt32(data["apid"]);
                failureAp.failureid = Convert.IsDBNull(data["failureid"]) ? -99 : Convert.ToInt32(data["failureid"]);

                listFailureAP.Add(failureAp);
            }
            return listFailureAP;
        }

        public List<DbDataTypes.FailureApInfo> GetAPFailureList(
            int _apid,
            int _failureType = -1,
            string _createTime = "",
            int _uid = 0)
        {
            List<DbDataTypes.FailureApInfo> listAPFailure = null;
            List<object> list = null;


            string failureTypeCondition = "";
            string createTimeCondition = "";
            if (_failureType != -1)
                failureTypeCondition = " and l.id = @failureid ";
            if (_createTime != null && _createTime.Length != 0)
                createTimeCondition = " and createtime > @createTime";

            string strSqlCmd = "select a.id,a.apid,a.failureid,a.description,a.createtime,l.failurename,l.level,l.failuredescription " +
                 " from AP_failure_assoc a left join  AP_failure_list l on a.failureid = l.id  where a.apid = @apid " +
               failureTypeCondition + createTimeCondition;

            SQLiteParameter[] para = { new SQLiteParameter("apid",_apid),
                                       new SQLiteParameter("failureid",_failureType),
                                       new SQLiteParameter("createTime",_createTime),
                                     };

            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, para, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            listAPFailure = new List<DbDataTypes.FailureApInfo>();
            foreach (object o in list)
            {
                DbDataTypes.FailureApInfo failureAp = new DbDataTypes.FailureApInfo();
                Dictionary<string, object> data = (Dictionary<string, object>)o;

                failureAp.id = Convert.IsDBNull(data["id"]) ? -99 : Convert.ToInt32(data["id"]);
                failureAp.apid = Convert.IsDBNull(data["apid"]) ? -99 : Convert.ToInt32(data["apid"]);
                failureAp.failureid = Convert.IsDBNull(data["failureid"]) ? -99 : Convert.ToInt32(data["failureid"]);
                failureAp.failurename = data["failurename"].ToString();
                failureAp.level = Convert.IsDBNull(data["level"]) ? -99 : Convert.ToInt32(data["level"]);
                failureAp.failuredescription = data["failuredescription"].ToString();
                failureAp.description = data["description"].ToString();
                failureAp.createtime = data["createtime"].ToString();

                listAPFailure.Add(failureAp);
            }
            return listAPFailure;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="filureid"></param>
        /// <param name="action"> 0-清空 1-删除一条</param>
        /// <returns></returns>
        public bool ClearFailureAP(int uid, int filureid,int action = 1)
        {
            int count = 0;
            try
            {
                if (action == 0)
                {
                    count = _mgr.Delete("AP_failure_assoc", null, null);
                }
                else
                {
                    count = _mgr.Delete("AP_failure_assoc", "id=@id", new SQLiteParameter[] {
                        new SQLiteParameter("id",filureid)});
                }
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }

            if (count <= 0)
                return false;
            return true;
        }

        public List<DbDataTypes.SearchTarget> GetSearchTargetBy(int uid, string table)
        {
            List<DbDataTypes.SearchTarget> stList = null;
            List<object> list = null;
            string strSqlCmd = "select * from " + table;
            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, null, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            stList = new List<DbDataTypes.SearchTarget>();
            foreach (object o in list)
            {
                DbDataTypes.SearchTarget st = new DbDataTypes.SearchTarget();
                Dictionary<string, object> data = (Dictionary<string, object>)o;

                st.id = Convert.IsDBNull(data["id"]) ? -99 : Convert.ToInt32(data["id"]);
                st.from = data["from"].ToString();
                st.to = data["to"].ToString();

                stList.Add(st);
            }
            return stList;
        }

        public List<DbDataTypes.SearchTarget> GetSearchTarget(int uid)
        {
            return GetSearchTargetBy(uid, "ap_search_target");
        }

        public List<DbDataTypes.SearchTarget> GetApIpPoolSearchTarget(int uid)
        {
            return GetSearchTargetBy(uid, "ap_ip_pool_search_target");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="st"></param>
        /// <param name="action">0 增 ，1 改</param>
        /// <returns></returns>
        public bool AddSearchTargetBy(int uid, DbDataTypes.SearchTarget st, string table,int action = 0)
        {
            bool ret = false;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("from", st.from);
            entity.Add("to", st.to);
            try
            {
                if (action == 0)
                {
                    ret = _mgr.Save(table, entity);
                }
                else if (action == 1)
                {
                    int count = _mgr.Update(table, entity, "id=@id", new System.Data.SQLite.SQLiteParameter[] { 
                        new SQLiteParameter("id",st.id)});
                    if (count <= 0)
                        ret = false;
                }

            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return ret;
        }

        public bool AddSearchTarget(int uid, DbDataTypes.SearchTarget st, int action = 0)
        {
            return AddSearchTargetBy(uid, st, "ap_search_target", action);
        }

        public bool AddApIpPoolSearchTarget(int uid, DbDataTypes.SearchTarget st, int action = 0)
        {
            return AddSearchTargetBy(uid, st, "ap_ip_pool_search_target", action);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="stid"></param>
        /// <param name="action">0 清空，1删一条</param>
        /// <returns></returns>
        public bool DelSearchTargetBy(int uid, int stid, string table, int action = 1)
        {
            int count = 0;
            try
            {
                if (action == 0)
                {
                    count = _mgr.Delete(table, null, null);
                }
                else
                {
                    count = _mgr.Delete(table, "id=@id", new SQLiteParameter[] {
                        new SQLiteParameter("id",stid)});
                }
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }

            if (count <= 0)
                return false;
            return true;
        }

        public bool DelSearchTarget(int uid, int stid, int action = 1)
        {
            return DelSearchTargetBy(uid, stid, "ap_search_target", action);
        }

        public bool DelApIpPoolSearchTarget(int uid, int stid, int action = 1)
        {
            return DelSearchTargetBy(uid, stid, "ap_ip_pool_search_target", action);
        }


        public List<DbDataTypes.LogInfo> ShowSystemLog(int uid, int fromid, int toid)
        {
            List<DbDataTypes.LogInfo> log = null;
            return log;
        }


        public DbDataTypes.Wan GetApWan(int _uid, int apid)
        {
            DbWrapper.DbDataTypes.Wan info = null;

            Dictionary<string, object> entity = null;

            try
            {
                entity = this._mgr.QueryOne("ap_wan_info", "apid", apid);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            if (entity != null)
            {
                info = new DbWrapper.DbDataTypes.Wan();
                info.id = Convert.IsDBNull(entity["id"]) ? -99 : Convert.ToInt32(entity["id"]);
                info.apid = Convert.IsDBNull(entity["apid"]) ? -99 : Convert.ToInt32(entity["apid"]);
                info.type = Convert.IsDBNull(entity["type"]) ? -99 : Convert.ToInt32(entity["type"]);
                info.ip = entity["ip"].ToString();
                info.netmask = entity["netmask"].ToString();
                info.geteway = entity["geteway"].ToString();
                info.dns = entity["dns"].ToString();
                info.standby_dbs = entity["standby_dbs"].ToString();
                info.hostname = entity["hostname"].ToString();
                info.pppoe_account = entity["pppoe_account"].ToString();
                info.pppoe_password = entity["pppoe_password"].ToString();
                info.pppoe_mtu = entity["pppoe_mtu"].ToString();
                info.pppoe_mss = entity["pppoe_mss"].ToString();
            }
            return info;
        }
        /// <summary>
        /// 新增或修改
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="wan"></param>
        /// <returns></returns>
        public bool SetWan(int uid, DbDataTypes.Wan wan)
        {
            int count = -1;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("apid", wan.apid);
            entity.Add("type", wan.type);
            entity.Add("ip", wan.ip);
            entity.Add("netmask", wan.netmask);
            entity.Add("geteway", wan.geteway);
            entity.Add("dns", wan.dns);
            entity.Add("standby_dbs", wan.standby_dbs);
            entity.Add("hostname", wan.hostname);
            entity.Add("pppoe_account", wan.pppoe_account);
            entity.Add("pppoe_password", wan.pppoe_password);
            entity.Add("pppoe_mtu", wan.pppoe_mtu);
            entity.Add("pppoe_mss", wan.pppoe_mss);

            try
            {
                count = _mgr.Update("ap_wan_info", entity, "apid=@apid", new System.Data.SQLite.SQLiteParameter[] { 
                        new SQLiteParameter("apid",wan.apid)});
                if (count <= 0)
                    _mgr.Save("ap_wan_info", entity);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return true;
        }

        public DbDataTypes.Dhcp GetApDhcp(int _uid, int apid)
        {
            DbWrapper.DbDataTypes.Dhcp info = null;

            Dictionary<string, object> entity = null;

            try
            {
                entity = this._mgr.QueryOne("ap_dhcp_info", "apid", apid);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            if (entity != null)
            {
                info = new DbWrapper.DbDataTypes.Dhcp();
                info.id = Convert.IsDBNull(entity["id"]) ? -99 : Convert.ToInt32(entity["id"]);
                info.apid = Convert.IsDBNull(entity["apid"]) ? -99 : Convert.ToInt32(entity["apid"]);
                info.enable = entity["enable"].ToString();
                info.ipbegin = entity["ipbegin"].ToString();
                info.ipend = entity["ipend"].ToString();
                info.addressLease = Convert.IsDBNull(entity["addressLease"]) ? -99 : Convert.ToInt32(entity["addressLease"]);
            }
            return info;
        }
        /// <summary>
        /// 新增或修改
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="wan"></param>
        /// <returns></returns>
        public bool SetDhcp(int uid, DbDataTypes.Dhcp dhcp)
        {
            int count = -1;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("apid", dhcp.apid);
            entity.Add("enable", dhcp.enable);
            entity.Add("ipbegin", dhcp.ipbegin);
            entity.Add("ipend", dhcp.ipend);
            entity.Add("addressLease", dhcp.addressLease);

            try
            {
                count = _mgr.Update("ap_dhcp_info", entity, "apid=@apid", new System.Data.SQLite.SQLiteParameter[] { 
                        new SQLiteParameter("apid",dhcp.apid)});
                if (count <= 0)
                    _mgr.Save("ap_dhcp_info", entity);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return true;
        }

        /// <summary>
        /// 系统日志查询函数
        /// </summary>
        /// <param name="uid">登入用户的id</param>
        /// <param name="username">待查询的用户</param>
        /// <param name="level">待查询的级别</param>
        /// <param name="lgtype">待查询的类型</param>
        /// <param name="timeStart">待查询的开始时间</param>
        /// <param name="timeEnd">待查询结束时间</param>
        /// <returns>日志链表</returns>
        public List<DbWrapper.DbDataTypes.LogInfo> SelectLog(
            int uid,
            string username,
            int level,
            int lgtype,
            string timeStart,
            string timeEnd)
        {
            List<DbWrapper.DbDataTypes.LogInfo> logInfoList = new List<DbWrapper.DbDataTypes.LogInfo>();
            DbDataTypes.LogInfo logInfo = null;
            List<object> list = null;

            string logtypeCondition = "",
                levelCondition = "",
                 userCondition = "";
            if (lgtype != SnmpForm.DbWrapper.DbDataTypes.LogInfo.TYPE.UNKNOWN)
                logtypeCondition = " and logtype = @logtype";
            if (level != SnmpForm.DbWrapper.DbDataTypes.LogInfo.LEVEL.UNKNOWN)
                levelCondition = " and level = @level";
            if (username != null && username.Length != 0)
                userCondition = " and username like @username";

            string strSqlCmd = "select log.id,log.userid,log.logtype,log.level,log.loginfo,log.datetime" +
                " from system_log_info log left join system_user_info user on user.id = log.userid" +
                " where datetime <= @timeEnd and datetime >= @timeStart" + logtypeCondition + levelCondition + userCondition;
            SQLiteParameter[] para = { new SQLiteParameter("username",username),
                                       new SQLiteParameter("logtype",lgtype),
                                       new SQLiteParameter("level",level),
                                       new SQLiteParameter("username","%"+username+"%"),
                                       new SQLiteParameter("timeEnd",timeEnd),
                                       new SQLiteParameter("timeStart",timeStart)
                                     };
            try
            {
                list = this._mgr.ExecuteRow(strSqlCmd, para, null);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message); ;
                return null;
            }
            if (list.Count == 0)
            {
                return null;
            }
            foreach (object o in list)
            {
                logInfo = new DbDataTypes.LogInfo();

                Dictionary<string, object> data = (Dictionary<string, object>)o;

                logInfo.id = Convert.IsDBNull(data["id"]) ? -99 : Convert.ToInt32(data["id"]);
                logInfo.userid = Convert.IsDBNull(data["userid"]) ? -99 : Convert.ToInt32(data["userid"]);
                logInfo.logtype = Convert.IsDBNull(data["logtype"]) ? -99 : Convert.ToInt32(data["logtype"]);
                logInfo.level = Convert.IsDBNull(data["level"]) ? -99 : Convert.ToInt32(data["level"]);
                logInfo.loginfo = data["loginfo"].ToString();
                logInfo.datetime = data["datetime"].ToString();
                logInfoList.Add(logInfo);
            }
            return logInfoList;
        }

        public DbDataTypes.Advance_tools GetAdvanceTools(int _uid, int apid)
        {
            DbWrapper.DbDataTypes.Advance_tools info = null;

            Dictionary<string, object> entity = null;

            try
            {
                entity = this._mgr.QueryOne("ap_advance_tools_info", "apid", apid);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return null;
            }
            if (entity != null)
            {
                info = new DbWrapper.DbDataTypes.Advance_tools();
                info.id = Convert.IsDBNull(entity["id"]) ? -99 : Convert.ToInt32(entity["id"]);
                info.apid = Convert.IsDBNull(entity["apid"]) ? -99 : Convert.ToInt32(entity["apid"]);
                info.led_light = Convert.IsDBNull(entity["led_light"]) ? false : Convert.ToBoolean(entity["led_light"]);
            }
            return info;
        }

        public bool SetAdvanceTools(int uid, DbDataTypes.Advance_tools at)
        {
            int count = -1;
            Dictionary<string, object> entity = new Dictionary<string, object>();
            entity.Add("apid", at.apid);
            entity.Add("led_light", at.led_light);

            try
            {
                count = _mgr.Update("ap_advance_tools_info", entity, "apid=@apid", new System.Data.SQLite.SQLiteParameter[] { 
                        new SQLiteParameter("apid",at.apid)});
                if (count <= 0)
                    _mgr.Save("ap_advance_tools_info", entity);
            }
            catch (SQLiteException es)
            {
                WriteMessage(DbDataTypes.LogInfo.LEVEL.ERROR,
                    DbDataTypes.LogInfo.TYPE.SYSTEM,
                    es.Message);
                return false;
            }
            return true;
        }
    }       

}